/**
 * @swagger
 * /api/asset/personnel-stats:
 *   get:
 *     tags:
 *       - 资产管理
 *     summary: 获取资产人员统计数据
 *     description: 统计资产使用人员、责任人和管理情况
 *     parameters:
 *       - in: query
 *         name: startDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始日期 (YYYY-MM-DD)
 *       - in: query
 *         name: endDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束日期 (YYYY-MM-DD)
 *     responses:
 *       200:
 *         description: 成功获取人员统计数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
 *                   type: integer
 *                   example: 200
 *                   description: 响应状态码
 *                 message:
 *                   type: string
 *                   example: "人员统计获取成功"
 *                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     overview:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 统计概览标题
 *                         statistics:
 *                           type: object
 *                           properties:
 *                             totalAssets:
 *                               type: integer
 *                               description: 总资产数
 *                             totalKeepers:
 *                               type: integer
 *                               description: 总领用人数
 *                             assetsWithKeeper:
 *                               type: integer
 *                               description: 已分配领用人的资产数
 *                             unassignedAssets:
 *                               type: integer
 *                               description: 未分配领用人的资产数
 *                             assignmentRatio:
 *                               type: string
 *                               description: 分配比例(%)
 *                             avgAssetsPerKeeper:
 *                               type: string
 *                               description: 平均每人领用资产数
 *                     topKeepersByCount:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 资产数量排行标题
 *                         keepers:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               keeperName:
 *                                 type: string
 *                                 description: 领用人姓名
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 财政拨款总额
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               totalArea:
 *                                 type: string
 *                                 description: 总面积
 *                     topKeepersByValue:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 资产价值排行标题
 *                         keepers:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               keeperName:
 *                                 type: string
 *                                 description: 领用人姓名
 *                               assetCount:
 *                                 type: integer
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 财政拨款总额
 *                               avgValue:
 *                                 type: string
 *                                 description: 平均价值
 *                               totalArea:
 *                                 type: string
 *                                 description: 总面积
 *                     keeperDistribution:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 领用人分布标题
 *                         ranges:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetCountRange:
 *                                 type: string
 *                                 description: 资产数量范围
 *                               keeperCount:
 *                                 type: integer
 *                                 description: 该范围内的领用人数
 *                               totalAssetsInRange:
 *                                 type: integer
 *                                 description: 该范围内的总资产数
 *                               avgAssetsInRange:
 *                                 type: string
 *                                 description: 该范围内平均资产数
 *                               totalValueInRange:
 *                                 type: string
 *                                 description: 该范围内总价值
 *                               keeperPercentage:
 *                                 type: string
 *                                 description: 领用人占比(%)
 *                     keeperCategoryRelation:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 领用人与分类关系标题
 *                         relations:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               keeperName:
 *                                 type: string
 *                                 description: 领用人姓名
 *                               categoryCount:
 *                                 type: integer
 *                                 description: 涉及分类数
 *                               totalAssets:
 *                                 type: integer
 *                                 description: 总资产数
 *                               categories:
 *                                 type: string
 *                                 description: 分类列表
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               diversityIndex:
 *                                 type: string
 *                                 description: 多样性指数
 *                     unassignedAssets:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 未分配资产标题
 *                         statistics:
 *                           type: object
 *                           properties:
 *                             unassignedCount:
 *                               type: integer
 *                               description: 未分配资产数
 *                             unassignedValue:
 *                               type: string
 *                               description: 未分配资产总价值
 *                             unassignedFiscalFunding:
 *                               type: string
 *                               description: 未分配资产财政拨款
 *                             avgUnassignedValue:
 *                               type: string
 *                               description: 未分配资产平均价值
 *                             unassignedCategories:
 *                               type: integer
 *                               description: 未分配资产分类数
 *                             unassignedRatio:
 *                               type: string
 *                               description: 未分配比例(%)
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
import { success, error as errorResponse, handleResponse } from '../../../lib/response';
import { query } from '../../../lib/db';

/**
 * 处理资产人员统计请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    const { startDate, endDate } = req.query;

    let whereClause = '';
    const params = [];

    if (startDate && endDate) {
      whereClause = 'WHERE PURCHASE_DATE BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause = 'WHERE PURCHASE_DATE >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause = 'WHERE PURCHASE_DATE <= ?';
      params.push(endDate);
    }
    
    // 领用人基础统计
    const personnelBasicQuery = `
      SELECT 
        COUNT(*) as totalAssets,
        COUNT(DISTINCT BASE_TEACHER_NAME_KEEPER) as totalKeepers,
        COUNT(CASE WHEN BASE_TEACHER_NAME_KEEPER IS NOT NULL AND BASE_TEACHER_NAME_KEEPER != '' THEN 1 END) as assetsWithKeeper,
        AVG(assets_per_keeper) as avgAssetsPerKeeper
      FROM (
        SELECT BASE_TEACHER_NAME_KEEPER, COUNT(*) as assets_per_keeper
        FROM wd_sec_assets12
        WHERE BASE_TEACHER_NAME_KEEPER IS NOT NULL AND BASE_TEACHER_NAME_KEEPER != '' ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
        GROUP BY BASE_TEACHER_NAME_KEEPER
      ) keeper_stats,
      (SELECT COUNT(*) as totalAssets FROM wd_sec_assets12 ${whereClause}) total_stats
    `;

    const personnelBasicResult = await query(personnelBasicQuery, [...params, ...params]);
    const personnelBasicData = personnelBasicResult[0];

    // 领用人资产数量统计Top20
    const topKeepersCountQuery = `
      SELECT 
        COALESCE(BASE_TEACHER_NAME_KEEPER, '未分配') as keeperName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue,
        SUM(COALESCE(CHEKMJ, 0)) as totalArea
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY BASE_TEACHER_NAME_KEEPER
      ORDER BY assetCount DESC
      LIMIT 20
    `;

    const topKeepersCountResult = await query(topKeepersCountQuery, params);

    // 领用人资产价值统计Top20
    const topKeepersValueQuery = `
      SELECT 
        BASE_TEACHER_NAME_KEEPER as keeperName,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgValue,
        SUM(COALESCE(CHEKMJ, 0)) as totalArea
      FROM wd_sec_assets12
      WHERE BASE_TEACHER_NAME_KEEPER IS NOT NULL 
        AND BASE_TEACHER_NAME_KEEPER != ''
        AND STOCK_PRICE > 0 ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
      GROUP BY BASE_TEACHER_NAME_KEEPER
      ORDER BY totalValue DESC
      LIMIT 20
    `;

    const topKeepersValueResult = await query(topKeepersValueQuery, params);

    // 领用人分布分析
    const keeperDistributionQuery = `
      SELECT 
        CASE 
          WHEN asset_count = 1 THEN '1个资产'
          WHEN asset_count BETWEEN 2 AND 5 THEN '2-5个资产'
          WHEN asset_count BETWEEN 6 AND 10 THEN '6-10个资产'
          WHEN asset_count BETWEEN 11 AND 20 THEN '11-20个资产'
          WHEN asset_count BETWEEN 21 AND 50 THEN '21-50个资产'
          ELSE '50个以上资产'
        END as assetCountRange,
        COUNT(*) as keeperCount,
        SUM(asset_count) as totalAssetsInRange,
        AVG(asset_count) as avgAssetsInRange,
        SUM(total_value) as totalValueInRange
      FROM (
        SELECT 
          BASE_TEACHER_NAME_KEEPER,
          COUNT(*) as asset_count,
          SUM(COALESCE(STOCK_PRICE, 0)) as total_value
        FROM wd_sec_assets12
        WHERE BASE_TEACHER_NAME_KEEPER IS NOT NULL AND BASE_TEACHER_NAME_KEEPER != '' ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
        GROUP BY BASE_TEACHER_NAME_KEEPER
      ) keeper_stats
      GROUP BY 
        CASE 
          WHEN asset_count = 1 THEN '1个资产'
          WHEN asset_count BETWEEN 2 AND 5 THEN '2-5个资产'
          WHEN asset_count BETWEEN 6 AND 10 THEN '6-10个资产'
          WHEN asset_count BETWEEN 11 AND 20 THEN '11-20个资产'
          WHEN asset_count BETWEEN 21 AND 50 THEN '21-50个资产'
          ELSE '50个以上资产'
        END
      ORDER BY 
        CASE 
          WHEN assetCountRange = '1个资产' THEN 1
          WHEN assetCountRange = '2-5个资产' THEN 2
          WHEN assetCountRange = '6-10个资产' THEN 3
          WHEN assetCountRange = '11-20个资产' THEN 4
          WHEN assetCountRange = '21-50个资产' THEN 5
          ELSE 6
        END
    `;

    const distributionResult = await query(keeperDistributionQuery, params);

    // 领用人与资产分类关系
    const keeperCategoryQuery = `
      SELECT 
        BASE_TEACHER_NAME_KEEPER as keeperName,
        COUNT(DISTINCT ASSETS_SORT_GGJ_NAME) as categoryCount,
        COUNT(*) as totalAssets,
        GROUP_CONCAT(DISTINCT ASSETS_SORT_GGJ_NAME ORDER BY ASSETS_SORT_GGJ_NAME SEPARATOR ', ') as categories,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      WHERE BASE_TEACHER_NAME_KEEPER IS NOT NULL AND BASE_TEACHER_NAME_KEEPER != '' ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
      GROUP BY BASE_TEACHER_NAME_KEEPER
      HAVING totalAssets >= 5
      ORDER BY categoryCount DESC, totalAssets DESC
      LIMIT 15
    `;

    const keeperCategoryResult = await query(keeperCategoryQuery, params);

    // 无人领用的资产统计
    const unassignedAssetsQuery = `
      SELECT 
        COUNT(*) as unassignedCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as unassignedValue,
        SUM(COALESCE(CAIZXJFLY, 0)) as unassignedFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgUnassignedValue,
        COUNT(DISTINCT ASSETS_SORT_GGJ_NAME) as unassignedCategories
      FROM wd_sec_assets12
      WHERE (BASE_TEACHER_NAME_KEEPER IS NULL OR BASE_TEACHER_NAME_KEEPER = '') ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
    `;

    const unassignedResult = await query(unassignedAssetsQuery, params);
    const unassignedData = unassignedResult[0];

    // 获取总资产数用于计算比例
    const totalAssetsQuery = `SELECT COUNT(*) as totalAssets FROM wd_sec_assets12 ${whereClause}`;
    const totalAssetsResult = await query(totalAssetsQuery, params);
    const totalAssets = totalAssetsResult[0].totalAssets;

    const responseData = {
      overview: {
        title: "领用人管理统计概览",
        statistics: {
          totalAssets: parseInt(totalAssets),
          totalKeepers: parseInt(personnelBasicData.totalKeepers || 0),
          assetsWithKeeper: parseInt(personnelBasicData.assetsWithKeeper || 0),
          unassignedAssets: parseInt(unassignedData.unassignedCount || 0),
          assignmentRatio: ((parseInt(personnelBasicData.assetsWithKeeper || 0) / parseInt(totalAssets)) * 100).toFixed(2),
          avgAssetsPerKeeper: parseFloat(personnelBasicData.avgAssetsPerKeeper || 0).toFixed(2)
        }
      },
      topKeepersByCount: {
        title: "资产数量最多的领用人(Top20)",
        keepers: topKeepersCountResult.map(row => ({
          keeperName: row.keeperName || '未分配',
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          totalArea: parseFloat(row.totalArea || 0).toFixed(2)
        }))
      },
      topKeepersByValue: {
        title: "资产价值最高的领用人(Top20)",
        keepers: topKeepersValueResult.map(row => ({
          keeperName: row.keeperName,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          avgValue: parseFloat(row.avgValue || 0).toFixed(2),
          totalArea: parseFloat(row.totalArea || 0).toFixed(2)
        }))
      },
      keeperDistribution: {
        title: "领用人资产分布",
        ranges: distributionResult.map(row => ({
          assetCountRange: row.assetCountRange,
          keeperCount: parseInt(row.keeperCount),
          totalAssetsInRange: parseInt(row.totalAssetsInRange),
          avgAssetsInRange: parseFloat(row.avgAssetsInRange || 0).toFixed(2),
          totalValueInRange: parseFloat(row.totalValueInRange || 0).toFixed(2),
          keeperPercentage: ((parseInt(row.keeperCount) / parseInt(personnelBasicData.totalKeepers || 1)) * 100).toFixed(2)
        }))
      },
      keeperCategoryRelation: {
        title: "领用人与资产分类关系(≥5个资产)",
        relations: keeperCategoryResult.map(row => ({
          keeperName: row.keeperName,
          categoryCount: parseInt(row.categoryCount),
          totalAssets: parseInt(row.totalAssets),
          categories: row.categories || '',
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          diversityIndex: (parseInt(row.categoryCount) / parseInt(row.totalAssets)).toFixed(2)
        }))
      },
      unassignedAssets: {
        title: "未分配资产统计",
        statistics: {
          unassignedCount: parseInt(unassignedData.unassignedCount || 0),
          unassignedValue: parseFloat(unassignedData.unassignedValue || 0).toFixed(2),
          unassignedFiscalFunding: parseFloat(unassignedData.unassignedFiscalFunding || 0).toFixed(2),
          avgUnassignedValue: parseFloat(unassignedData.avgUnassignedValue || 0).toFixed(2),
          unassignedCategories: parseInt(unassignedData.unassignedCategories || 0),
          unassignedRatio: ((parseInt(unassignedData.unassignedCount || 0) / parseInt(totalAssets)) * 100).toFixed(2)
        }
      }
    };

    return handleResponse(res, success(responseData, "资产人员管理统计获取成功"));
    
  } catch (error) {
    console.error('Asset personnel stats error:', error);
    return handleResponse(res, errorResponse("获取资产人员管理统计失败", 500));
  }
}

export default withCors(handler, ['GET']);